//
//  GMDataBaseTool.m
//  GDataBaseDemo
//
//  Created by 高明 on 2020/4/7.
//  Copyright © 2020 WDTX. All rights reserved.
//

#import "GMDataBaseTool.h"
#import "AreaModel.h"

static NSString *rootFolderName = @"DataBase/";

@implementation GMDataBaseTool

+(instancetype) sharedTool {
    static GMDataBaseTool *instance;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[GMDataBaseTool alloc] init];
    });
    return instance;
}

-(instancetype)init {
    if (self = [super init]) {
        self.queue = [FMDatabaseQueue databaseQueueWithPath:[self getFullPath]];
    }
    return self;
}

-(NSString *)getFullPath {
    //创建文件夹，以便于可以清除缓存
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    //创建文件夹
    NSString *rootFilePath = [documentsDirectory stringByAppendingPathComponent:rootFolderName];
    NSString *fullPath = [NSString stringWithFormat:@"%@database.db",rootFilePath];
    return fullPath;
}

-(void)createTable {
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        BOOL res = [db executeStatements:@"CREATE TABLE IF NOT EXISTS 'AreaTable' (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'name' text,'code' text)"];
        if (res) {
            NSLog(@"创建AreaTable表成功");
        }
    }];
}

-(void)insertDataWithModel:(AreaModel *)model {
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db open]) {
            BOOL isOK = [db executeStatements:[NSString stringWithFormat:@"INSERT INTO AreaTable (id,name,code) VALUES('%@','%@','%@')",model.areaId,model.name,model.code]];
            if (isOK) {
                NSLog(@"插入成功");
            } else {
                NSLog(@"插入失败,可能数据库中已经存在该数据的主键");
            }
        }
        [db close];
    }];
}

-(void)deleteDataWithModel:(AreaModel *)model {
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db open]) {
            BOOL isOK = [db executeStatements:[NSString stringWithFormat:@"DELETE FROM AreaTable WHERE id = %zd",model.areaId.integerValue]];
            if (isOK) {
                NSLog(@"删除成功");
            } else {
                NSLog(@"删除失败");
            }
        }
        [db close];
    }];
}

-(void)deleteTable {
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        BOOL res = [db executeStatements:@"DELETE FROM AreaTable"];
        if (res) {
            NSLog(@"删除成功");
        } else {
            NSLog(@"删除失败");
        }
    }];
//    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
//        if ([db open]) {
//            BOOL isOK = [db executeStatements:@"DELETE FROM AreaTable"];
//            if (isOK) {
//                NSLog(@"删除成功");
//            } else {
//                NSLog(@"删除失败");
//            }
//        }
//        [db close];
//    }];
}

-(void)updateDataWithModel:(AreaModel *)model {
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db open]) {
            NSString *selectSql = [NSString stringWithFormat:@"SELECT * FROM AreaTable WHERE id = %zd",model.areaId.integerValue];
            FMResultSet *set = [db executeQuery:selectSql];
            if ([set next]) { //if next为真，查到了结果集 //
                NSString *modifySql = [NSString stringWithFormat:@"UPDATE AreaTable SET name = ?,code = ? WHERE id = %zd",model.areaId.integerValue];//model.name,model.code,
                BOOL isOK = [db executeUpdate:modifySql withArgumentsInArray:@[model.name,model.code]];
                if (isOK) {
                    NSLog(@"修改成功");
                } else {
                    NSLog(@"修改失败");
                }
            } else {
                NSLog(@"未查到需要修改的数据");
            }
        }
        [db close];
    }];
}

-(NSArray *) getAllData {
    __block NSMutableArray *muArr = [NSMutableArray array];
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db open]) {
            FMResultSet *set = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM AreaTable"]];
            while ([set next]) {
                AreaModel *model = [[AreaModel alloc] init];
                model.areaId = [set stringForColumn:@"id"];
                model.name = [set stringForColumn:@"name"];
                model.code = [set stringForColumn:@"code"];
                [muArr addObject:model];
            }
        }
        [db close];
    }];
    return muArr.copy;
}

-(AreaModel *) getModelWithID:(NSString *)areaId {
    __block AreaModel *model = [[AreaModel alloc] init];
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db open]) {
            FMResultSet *set = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM AreaTable WHERE id = %zd",areaId.integerValue]];
            if ([set next]) {
                model.areaId = [set stringForColumn:@"id"];
                model.name = [set stringForColumn:@"name"];
                model.code = [set stringForColumn:@"code"];
            } else {
                model = nil;
            }
            [set close];
        }
        [db close];
    }];
    return model;
}

-(NSArray *)getLikeDataWithName:(NSString *)name {
    __block NSMutableArray *muArr = [NSMutableArray array];
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db open]) {
            FMResultSet *set = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM AreaTable WHERE name LIKE '%%%@%%'",name]];
            while ([set next]) {
                AreaModel *model = [[AreaModel alloc] init];
                model.areaId = [set stringForColumn:@"id"];
                model.name = [set stringForColumn:@"name"];
                model.code = [set stringForColumn:@"code"];
                [muArr addObject:model];
            }
        }
        [db close];
    }];
    return muArr.copy;
}

@end
